﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Configuration;
namespace DAO
{
    public class DataProvider
    {
        #region "Bien"
        SqlConnection connect;//tao ket noi giua sql va visual
        public SqlConnection Connect
        {
            get { return connect; }
            set { connect = value; }
        }
        string sqlConnectionstring;//lay chuoi khai bao ket noi den csdl

        public string SqlConnectionstring
        {
            get { return sqlConnectionstring; }
            set { sqlConnectionstring = value; }
        }
        string error;//chua loi phat sinh do ket noi voi sql

        public string Error
        {
            get { return error; }
            set { error = value; }
        }
        #endregion
        public DataProvider()
        {
            //Ket noi truc tiep csdl
            sqlConnectionstring = "Data Source=.;Initial Catalog=WebSiteChiaSeFile;Integrated Security=True";
            //ket noi gian tiep thong qua doc file ConfigurationManager
            //sqlConnectionstring = ConfigurationManager.ConnectionStrings["strKetNoi"].ToString();
            connect = new SqlConnection(sqlConnectionstring);
        }
        public bool MoketNoi()
        {
            try
            {
                if (connect == null)
                    connect = new SqlConnection(sqlConnectionstring);
                if (connect.State == ConnectionState.Open)
                    connect.Close();
                connect.Open();
                return true;
            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message.ToString());
                return false;
            }

        }
        public bool DongKetNoi()
        {
            try
            {
                if (connect != null)
                    if (connect.State == ConnectionState.Open)
                        connect.Close();
                return true;
            }
            catch (SqlException ex)
            {
                error = ex.Message;
                return false;
            }
        }
        //public bool ExecuteNonQuery(string sql)
        public bool ExecuteNonQuery(string procname, SqlParameter[] sqlparam)
        {
            try
            {
                if (MoketNoi())
                {
                    SqlCommand cmd = connect.CreateCommand();
                    //cmd.CommandType = CommandType.Text;
                    cmd.CommandType = CommandType.StoredProcedure;
                    //cmd.CommandText = sql;
                    cmd.CommandText = procname;
                    if (sqlparam != null)
                    {
                        cmd.Parameters.AddRange(sqlparam);
                    }
                    cmd.ExecuteNonQuery();
                    error = "";
                }
                return true;
            }
            catch (SqlException ex)
            {
                error = ex.Message;
                throw new Exception(ex.Message.ToString());
                return false;
            }
            finally
            {
                DongKetNoi();
            }
        }
        //public DataTable ExecuteQuery(string sql)
        public DataTable ExecuteQuery(string procname, SqlParameter[] param)
        {
            DataTable tb = new DataTable();
            try
            {
                if (MoketNoi())
                {
                    SqlCommand cmd = connect.CreateCommand();
                    //cmd.CommandType = CommandType.Text;
                    cmd.CommandType = CommandType.StoredProcedure;
                    //cmd.CommandText = sql;
                    cmd.CommandText = procname;
                    if (param != null)
                    {
                        cmd.Parameters.AddRange(param);
                    }
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    da.Fill(tb);
                    error = "";
                }

            }

            catch (SqlException ex)
            {
                error = ex.Message;
                throw new Exception(ex.Message.ToString());
                //return false;

            }
            return tb;
        }
    }
}
